import java.io.File;
import java.sql.*;

import ecologylab.xml.ElementState;
import ecologylab.xml.TranslationScope;
import ecologylab.xml.XMLTranslationException;

import xmlParser.ColField;
import xmlParser.ColFieldType;
import xmlParser.DBSchema;
import xmlParser.FieldInteger;
import xmlParser.FieldString;
import xmlParser.Record;
import xmlParser.Table;
import xmlParser.TableType;
import xmlParser.TypedColField;
import xmlParser.TypedRecord;
import xmlParser.TypedTable;
import xmlParser.xmlParserTranslationScope;
public class RunDB {
	Connection connection;
	   Statement statement;
	   Statement statement2;

	   public void loadDriver() throws ClassNotFoundException{
	      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	   }

	 //edited here by Mark
	   public void makeConnection(String name) throws SQLException {
	      String driver = "jdbc:odbc:";
		   connection=
	         DriverManager.getConnection(driver+name);//"jdbc:odbc:student");
	   }
	   
	   //Bharat: to make it compatible if the database source is not mentioned
	   public void makeConnection() throws SQLException {
		      connection=
		         DriverManager.getConnection("jdbc:odbc:student");
		   }

	   public void buildStatement() throws SQLException {
	      statement = connection.createStatement();
	      statement2 = connection.createStatement();
	   }

	   //Bharat: to make it compatible with the earlier version of GUI
	   public void executeQuery(GUIv1 screen, String query) throws SQLException {

		      boolean foundResults =
		         statement.execute(query);
		      System.out.println(query);
		      if(foundResults){
		         ResultSet set = statement.getResultSet();
		         
		         statement2.execute(query);
		         ResultSet anotherset = statement2.getResultSet();
		         if(set!=null) {
		        	 displayResults(set);
		        	 displayonGUI(screen,anotherset);
		         }
		      }else {
		         connection.close();
		      }
		   }
	   
	 	//added boolean setXML
	   public void executeQuery(GUI screen, String query, boolean setXML) throws SQLException {

	      boolean foundResults =
	         statement.execute(query);
	      System.out.println(query);
	      if(foundResults){
	         ResultSet set = statement.getResultSet();
	         
	         statement2.execute(query);
	         ResultSet anotherset = statement2.getResultSet();
	         if(set!=null) {
	        	 //check to see if we need to output to xml or not
	        	 if (setXML == true){
	        		 displayResults(set);
	        	 }
	        	 displayonGUI(screen,anotherset);
	         }
	      }else {
	         connection.close();
	      }
	   }
	   
	   public void displayonGUI(GUI screen, ResultSet rs) throws SQLException {
	      ResultSetMetaData metaData = rs.getMetaData();
	      int columns=metaData.getColumnCount();
		   String resulttext = "";
		   int isfirst=1;
		   while(rs.next()){
	             if(isfirst==1) {
	            	 for(int i=1;i<=columns;++i) {
	            		 resulttext += metaData.getColumnName(i)+", ";
	            	 }
	            	 resulttext+="\n";
	            	 isfirst=0;
	             }
		         for(int i=1;i<=columns;++i) {

		        	// System.out.println(rs.getString(i));
		        	
		        	 //Without using the ecologylabFundamental lib.
//		            text+="<"+metaData.getColumnName(i)+">";
		            resulttext+=rs.getString(i)+", ";
//		             text+="</"+metaData.getColumnName(i)+">";
//		             text+="\n";
		         }
		         //Without using the ecologylabFundamental lib.
		         resulttext+="\n";
		   }
		   screen.printToScreen(resulttext);
	   }
	   
	   //to make it compatible with earlier version
	   public void displayonGUI(GUIv1 screen, ResultSet rs) throws SQLException {
		      ResultSetMetaData metaData = rs.getMetaData();
		      int columns=metaData.getColumnCount();
			   String resulttext = "";
			   int isfirst=1;
			   while(rs.next()){
		             if(isfirst==1) {
		            	 for(int i=1;i<=columns;++i) {
		            		 resulttext += metaData.getColumnName(i)+", ";
		            	 }
		            	 resulttext+="\n";
		            	 isfirst=0;
		             }
			         for(int i=1;i<=columns;++i) {

			        	// System.out.println(rs.getString(i));
			        	
			        	 //Without using the ecologylabFundamental lib.
//			            text+="<"+metaData.getColumnName(i)+">";
			            resulttext+=rs.getString(i)+", ";
//			             text+="</"+metaData.getColumnName(i)+">";
//			             text+="\n";
			         }
			         //Without using the ecologylabFundamental lib.
			         resulttext+="\n";
			   }
			   screen.printToScreen(resulttext);
		   }

	   void displayResults(ResultSet rs) throws SQLException {
	      ResultSetMetaData metaData = rs.getMetaData();
	      int columns=metaData.getColumnCount();
	      String resulttext="";

	      //Bharat
	      Table table = new Table();
	      TypedTable typedTable = new TypedTable();
	      File sampleFile = new File("metadata.xml");
	      File sampleFile1 = new File("readfrommetadata.xml");
	      int isfirst=1;
	      while(rs.next()){
	    	  Record record = new Record();
	    	  TypedRecord typedRecord = new TypedRecord();
	    	  
	    	 
	         for(int i=1;i<=columns;++i) {
	        	// System.out.println(rs.getString(i));
	        	
	        	 //Without using the ecologylabFundamental lib.
//	            text+="<"+metaData.getColumnName(i)+">";
//	            resulttext+=rs.getString(i)+" ";
//	             text+="</"+metaData.getColumnName(i)+">";
//	             text+="\n";
	             
	             //Bharat
	        	 /*
	        	 //Populating the DBSchema -- Has to be kept where the user is entering the data
	        	 // about the tables
	        	 DBSchema dbSchema = new DBSchema();
	        	 //create a tabletype
	        	 TableType tableType = new TableType();
	        	 //The DBSchema has to be populated either by the user or from querying DB.
	        	 //add name
	        	 //add ColFieldType
	        	 */
	        	 
	        	 if(isfirst == 1)
		    	  {
		    		  //populating the fieldTypes in table
	        		 ColFieldType colFieldType = new ColFieldType(metaData.getColumnName(i),
	        				 					sqlType(metaData.getColumnType(i)));
	        		 table.addfieldType(colFieldType);
	        		 
		    	  }
	        	 
	        	 //Populating the data structure for xml
	        	 
	             ColField colField = new ColField();
	             colField.setName(metaData.getColumnName(i));
	             colField.setValue(rs.getString(i));
	             record.addField(colField);
	             
	             //Populating the rightly typed data structure
	             switch(metaData.getColumnType(i))
	             {
	             case 12:/*VARCHAR*/
//	            	 if(rs.getString(i) == null)
//	            	 {
//	            		 System.out.println("debug");
//	            	 }
	            	 FieldString fieldString = new FieldString(metaData.getColumnName(i),
	            			 "VARCHAR", colField.getValue());
	            	 typedRecord.addField(fieldString);
	            	 break;
	             case 4:/*INTEGER*/
	            	 FieldInteger fieldInteger = new FieldInteger(metaData.getColumnName(i),
	            			 "INTEGER", Integer.parseInt(colField.getValue()));
	            	 typedRecord.addField(fieldInteger);
	            	 break;
	            	 //TODO! Have to code for other types
	             default: ;
	             }
	             
	         }
	         //Bharat
	         isfirst = 0;
	         table.addRecord(record);
	         typedTable.addRecord(typedRecord);
	         
	         //Without using the ecologylabFundamental lib.
//	         resulttext+="\n";
	      }
	      //System.out.println("these are the results");
	      //Without using the ecologylabFundamental lib.
	      System.out.println(resulttext);
	      //System.out.println("printed");
	      
	      //Bharat
	      try {
	    	  //Writing to xmlfile metadata.xml
			table.writePrettyXML(sampleFile);
			
			//Reading from metadata.xml and writing into readfrommetadata.xml
			final TranslationScope TS = xmlParserTranslationScope.get();
			Table newTable = (Table) ElementState.translateFromXML(sampleFile,TS);
			newTable.writePrettyXML(sampleFile1);
			
			TypedTable typedTablefromxml = new TypedTable();
			//Traversing accross records
			for(Record record : newTable.getRecords())
			{
				TypedRecord typedRecordfromxml = new TypedRecord();
				for(ColField colField : record.getFields())
				{
					//TypedColField typedColField = new TypedColField();
					Integer fieldType = newTable.getFieldTypeInt(colField.getName());
					switch(fieldType)
					{
					case 12:/*VARCHAR*/
//						if(rs.getString(i) == null)
//						{
//						System.out.println("debug");
//						}
						FieldString fieldString = new FieldString(colField.getName(),
								"VARCHAR", colField.getValue());
						typedRecordfromxml.addField(fieldString);
						break;
					case 4:/*INTEGER*/
						FieldInteger fieldInteger = new FieldInteger(colField.getName(),
								"INTEGER", Integer.parseInt(colField.getValue()));
						typedRecordfromxml.addField(fieldInteger);
						break;
						//TODO! Have to code for other types
					default: ;
					}
				}//fields
				typedTablefromxml.addRecord(typedRecordfromxml);
			}//records
			System.out.println(typedTablefromxml);

		} catch (XMLTranslationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	   }
	   
	   private String sqlType(Integer type)
	   {
		   switch(type)
		   {
		   case 12:/*VARCHAR*/
			   return "VARCHAR";
		   case 4:/*INTEGER*/
			   return "INTEGER";
		   default: 
			   return "WRONG";
		   }
	   }
	   
	   private Integer sqlTypeInt(String type)
	   {
		   if(type.equals("VARCHAR"))
			   return 12;
		   else if(type.equals("INTEGER"))
			   return 4;
		   return -1;
	   }

}
